<?php
/**
 * Gold Shipping Module
 *
 * NOTICE OF LICENSE
 *
 * This source file is subject to the Open Software License (OSL 3.0)
 * that is bundled with this package in the file LICENSE.txt.
 * It is also available through the world-wide-web at this URL:
 * http://opensource.org/licenses/osl-3.0.php
 * If you did not receive a copy of the license and are unable to
 * obtain it through the world-wide-web, please send an email
 * to license@magentocommerce.com so we can send you a copy immediately.
 *
 * DISCLAIMER
 *
 * Do not edit or add to this file if you wish to upgrade Magento to newer
 * versions in the future. If you wish to customize Magento for your
 * needs please refer to http://www.magentocommerce.com for more information.
 *
 * Shipping Fedex
 *
 * @category    Gold
 * @package     Gold_Cshipping
 * @copyright   Copyright (c) 2011 Gold
 * @Author $Id$
*/
class Gold_Cshipping_Model_Mysql4_Carrier_Fedex extends Mage_Core_Model_Mysql4_Abstract
{
    protected function _construct()
    {
        $this->_init('cshipping/fedex', 'pk');
    }

    public function getNewRate(Mage_Shipping_Model_Rate_Request $request,$zipRangeSet=0, $carrierId)
    {
        $read = $this->_getReadAdapter();
        $write = $this->_getWriteAdapter();

		$postcode = $request->getDestPostcode();
        $table = Mage::getSingleton('core/resource')->getTableName('cshipping/fedex');

		if ($zipRangeSet) {
			#  Want to search for postcodes within a range
			$zipSearchString = $read->quoteInto(" AND dest_zip<=? ", $postcode).
								$read->quoteInto(" AND dest_zip_to>=? )", $postcode);
		} else {
			$zipSearchString = $read->quoteInto(" AND ? LIKE dest_zip )", $postcode);
		}

    	if (!$this->hasRegions($request->getDestCountryId()))
		{
			$regionId = '';
		}
		else 
		{
			$regionId = $request->getDestRegionId();
		}
    	$deliveryzoneName = $this->_getZone($request->getDestCountryId(), $regionId, $carrierId);
		if (!$deliveryzoneName)
		{
			return;
		}
		$getConditionName = $request->getConditionName();
		$checkCondition = (is_array($getConditionName)||$getConditionName != 'package_weight') ? false : true;
		for ($j=0;$j<2;$j++)
		{

			$select = $read->select()->from($table);
			switch($j) {
				case 0:
					$select->where(
						$read->quoteInto(" (zone_id=? ", $deliveryzoneName).
							$zipSearchString
						);
					break;
				case 1:
					$select->where(
					   $read->quoteInto(" zone_id=? ", $deliveryzoneName)
					   );
					break;				
			}

			if (is_array($getConditionName)) {
				$i = 0;
				foreach ($getConditionName as $conditionName) {
					if ($i == 0) {
						$select->where('condition_name=?', $conditionName);
					} else {
						$select->orWhere('condition_name=?', $conditionName);
					}					
					if (!$checkCondition)
					{
						$select->where('condition_value<=?', $request->getData($conditionName));
					}


					$i++;
				}
			} else {
				$select->where('condition_name=?', $getConditionName);
				if (!$checkCondition)
				{
					$select->where('condition_value<=?', $request->getData($getConditionName));
					$select->where('condition_to_value>=?', $request->getData($getConditionName));
				}
				else 
				{
					$select->where('condition_value<=?', $request->getData($getConditionName));
				}				
			}

			$select->where('website_id=?', $request->getWebsiteId());

			$select->order('condition_value DESC');
			$select->limit(1);			
			/*
			pdo has an issue. we cannot use bind
			*/
			$newdata=array();
			$row = $read->fetchAll($select);
			if (!empty($row))
			{
				// have found a result or found nothing and at end of list!
				foreach ($row as $data) {
					if ($checkCondition)
					{
						//calculate shipping price
						$weight = $request->getData($getConditionName);
						if ($weight > $data['condition_value'] && $data['condition_value_2']>0)
						{
							$data['price'] += ceil((($weight-$data['condition_value'])/$data['condition_value_2']))*$data['price_2'];
						}
					}
					$newdata[]=$data;
				}
				break;
			}
		}
		return $newdata;

    }

    public function uploadAndImport(Varien_Object $object)
    {
        $csvFile = $_FILES["groups"]["tmp_name"]["goldfedex"]["fields"]["import"]["value"];

        if (!empty($csvFile)) {

            $csv = trim(file_get_contents($csvFile));

            $table = Mage::getSingleton('core/resource')->getTableName('cshipping/fedex');

            $websiteId = $object->getScopeId();
            $websiteModel = Mage::app()->getWebsite($websiteId);
            /*
            getting condition name from post instead of the following commented logic
            */

            if (isset($_POST['groups']['goldfedex']['fields']['condition_name']['inherit'])) {
                $conditionName = (string)Mage::getConfig()->getNode('default/carriers/goldfedex/condition_name');
            } else {
                $conditionName = $_POST['groups']['goldfedex']['fields']['condition_name']['value'];
            }

//            $conditionName = $object->getValue();
//            if ($conditionName{0} == '_') {
//                $conditionName = Mage::helper('core/string')->substr($conditionName, 1, strpos($conditionName, '/')-1);
//            } else {
//                $conditionName = $websiteModel->getConfig('carriers/goldshipping/condition_name');
//            }
            $conditionFullName = Mage::getModel('cshipping/carrier_fedex')->getCode('condition_name_short', $conditionName);
            if (!empty($csv)) {
                $exceptions = array();
                $csvLines = explode("\n", $csv);
                $csvLine = array_shift($csvLines);
                $csvLine = $this->_getCsvValues($csvLine);
                if (count($csvLine) < 5) {
                    $exceptions[0] = Mage::helper('shipping')->__('Invalid Fedex File Format');
                }

                $zoneNames = array();
                foreach ($csvLines as $k=>$csvLine) {
                    $csvLine = $this->_getCsvValues($csvLine);
                    if (count($csvLine) > 0 && count($csvLine) < 5) {
                        $exceptions[0] = Mage::helper('shipping')->__('Invalid Fedex File Format');
                    } else {
                        $zoneNames[] = $csvLine[0];
                    }
                }

                if (empty($exceptions)) {
                    $data = array();
                    $zoneNamesToIds = array();

                    $zoneCollection = Mage::getResourceModel('deliveryzone/zone_collection')->setType('S')->addZoneTitleFilter($zoneNames)->load();
                    foreach ($zoneCollection->getItems() as $zone) {
                        $zoneNamesToIds[$zone->getData('title')] = $zone->getData('zone_id');
                    }
     				

                    foreach ($csvLines as $k=>$csvLine) {

                        $csvLine = $this->_getCsvValues($csvLine);

                        if (empty($zoneNamesToIds) || !array_key_exists($csvLine[0], $zoneNamesToIds)) {
                            $zoneId = '0';
                            if ($csvLine[0] != '*' && $csvLine[0] != '') {
                                $exceptions[] = Mage::helper('shipping')->__('Invalid Zone "%s" in the Row #%s', $csvLine[0], ($k+1));
                            }
                        } else {
                            $zoneId = $zoneNamesToIds[$csvLine[0]];
                        }                        

						if (count($csvLine)==8) {	

							if ($csvLine[1] == '*' || $csvLine[1] == '') {
								$zip = '';
							} else {
								$zip = $csvLine[1];
							}


							if ($csvLine[2] == '*' || $csvLine[2] == '') {
								$zip_to = '';
							} else {
								$zip_to = $csvLine[2];
							}


							if (!$this->_isPositiveDecimalNumber($csvLine[3]) || $csvLine[3] == '*' || $csvLine[5] == '') {
								$exceptions[] = Mage::helper('shipping')->__('Invalid %s "%s" in the Row #%s', $conditionFullName, $csvLine[3], ($k+1));
							} else {
								$csvLine[3] = (float)$csvLine[3];
							}

							if (!$this->_isPositiveDecimalNumber($csvLine[4])) {
								$exceptions[] = Mage::helper('shipping')->__('Invalid %s To "%s" in the Row #%s', $conditionFullName, $csvLine[4], ($k+1));
							} else {
								$csvLine[4] = (float)$csvLine[4];
							}


							$data[] = array('website_id'=>$websiteId, 'zone_id'=>$zoneId, 'dest_zip'=>$zip, 'dest_zip_to'=>$zip_to, 'condition_name'=>$conditionName, 'condition_value'=>$csvLine[3],'condition_value_2'=>$csvLine[4], 'price'=>$csvLine[5], 'price_2'=>$csvLine[6], 'delivery_type'=>$csvLine[7]);

						}
						else {

							if ($csvLine[1] == '*' || $csvLine[1] == '') {
								$zip = '';
							} else {
								$zip = $csvLine[1]."%";
							}

							$zip_to = '';

							if (!$this->_isPositiveDecimalNumber($csvLine[3]) || $csvLine[3] == '*' || $csvLine[3] == '') {
								$exceptions[] = Mage::helper('shipping')->__('Invalid %s From "%s" in the Row #%s', $conditionFullName, $csvLine[3], ($k+1));
							} else {
								$csvLine[3] = (float)$csvLine[3];
							}

							if (!$this->_isPositiveDecimalNumber($csvLine[4])) {
								$exceptions[] = Mage::helper('shipping')->__('Invalid %s To "%s" in the Row #%s', $conditionFullName, $csvLine[4], ($k+1));
							} else {
								$csvLine[4] = (float)$csvLine[4];
							}
							$data[] = array('website_id'=>$websiteId, 'zone_id'=>$zoneId, 'dest_zip'=>$zip,'dest_zip_to'=>$zip_to,  'condition_name'=>$conditionName, 'condition_value'=>$csvLine[3],'condition_value_2'=>$csvLine[4], 'price'=>$csvLine[5], 'price_2'=>$csvLine[6], 'delivery_type'=>$csvLine[7]);
						}


						$dataDetails[] = array('zone'=>$csvLine[0]);

                    }
                }

                if (empty($exceptions)) {
                    $connection = $this->_getWriteAdapter();


                    $condition = array(
                        $connection->quoteInto('website_id = ?', $websiteId),
                        $connection->quoteInto('condition_name = ?', $conditionName),
                    );
                    $connection->delete($table, $condition);

                    foreach($data as $k=>$dataLine) {
                        try {
                           $connection->insert($table, $dataLine);
                        } catch (Exception $e) {
                            $exceptions[] = Mage::helper('shipping')->__('Duplicate Row #%s (Zone "%s", Zip From "%s", Zip To "%s", Delivery Type "%s", Value "%s" and Value Extra "%s")', ($k+1), $dataDetails[$k]['zone'], $dataLine['dest_zip'],  $dataLine['dest_zip_to'], $dataLine['delivery_type'], $dataLine['condition_value'], $dataLine['condition_value_2']);
                        }
                    }
                }
                if (!empty($exceptions)) {
                    throw new Exception( "\n" . implode("\n", $exceptions) );
                }
            }
        }
    }

    private function _getCsvValues($string, $separator=",")
    {
        $elements = explode($separator, trim($string));
        for ($i = 0; $i < count($elements); $i++) {
            $nquotes = substr_count($elements[$i], '"');
            if ($nquotes %2 == 1) {
                for ($j = $i+1; $j < count($elements); $j++) {
                    if (substr_count($elements[$j], '"') > 0) {
                        // Put the quoted string's pieces back together again
                        array_splice($elements, $i, $j-$i+1, implode($separator, array_slice($elements, $i, $j-$i+1)));
                        break;
                    }
                }
            }
            if ($nquotes > 0) {
                // Remove first and last quotes, then merge pairs of quotes
                $qstr =& $elements[$i];
                $qstr = substr_replace($qstr, '', strpos($qstr, '"'), 1);
                $qstr = substr_replace($qstr, '', strrpos($qstr, '"'), 1);
                $qstr = str_replace('""', '"', $qstr);
            }
            $elements[$i] = trim($elements[$i]);
        }
        return $elements;
    }

    private function _isPositiveDecimalNumber($n)
    {
        return preg_match ("/^[0-9]+(\.[0-9]*)?$/", $n);
    }
    
    /**
	 *Get Zone title by countryId and RegionId
     */
	private function _getZone($countryId, $regionId, $carrierId)
    {
    	if (empty($countryId))
    	{
    		return;
    	}
    	$read = $this->_getReadAdapter();
    	$locationsTable = Mage::getSingleton('core/resource')->getTableName('deliveryzone/location');
        $zoneTable = Mage::getSingleton('core/resource')->getTableName('deliveryzone/zone');
        $shippingMethodTable = Mage::getSingleton('core/resource')->getTableName('deliveryzone/shippingmethod');
        $_select = $read->select()->from(array('z'=>$zoneTable))
        			   ->joinLeft(array('l'=>$locationsTable), 'z.location_id = l.location_id')        			   
        			   ->joinLeft(array('s'=>$shippingMethodTable), 'z.zone_id = s.zone_id')  
        			   ->where("s.carrier_id = ?", $carrierId)      			   
        			   ->where("l.country_id = ?", $countryId);
        $regionId = intval($regionId);
        if (!empty($regionId))
        {
        	$_select->where("l.region_ids LIKE ? ",'%,'.$regionId.',%');
        }
        $_select->where("z.type = 'S'");
        $result = $read->fetchRow($_select);       
        return $result ? $result['zone_id'] : false;		           			   
    }
    
	protected function hasRegions($countryId)
    {
    	$regionCol = Mage::getModel('directory/region')->getResourceCollection()
                ->addCountryFilter($countryId)
                ->load();
    	$size = sizeof($regionCol);
    	return  $size > 0 ? true :false;  	    	
    }

}
